package org.dromara.web.service;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.collection.ListUtil;
import cn.hutool.core.lang.Assert;
import cn.hutool.core.map.MapUtil;
import cn.hutool.core.text.CharSequenceUtil;
import cn.hutool.core.thread.ThreadUtil;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import lombok.RequiredArgsConstructor;
import org.dromara.web.domain.vo.Table;
import org.dromara.web.domain.vo.TableDDL;
import org.dromara.web.domain.vo.TableDiffVo;
import org.dromara.web.mapper.MasterSQLExecMapper;
import org.dromara.web.mapper.TargetSQLExecMapper;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Optional;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.ThreadPoolExecutor;

@RequiredArgsConstructor
@Service
public class TableService {

    private static final String SELECT_FROM = "select * from ";
    private static final String WHERE = " where ";
    private static final String AND = " and ";
    private static final String DELETE_FROM = "delete from ";
    private final MasterSQLExecMapper masterSqlExecMapper;
    private final TargetSQLExecMapper targetSqlExecMapper;
    ThreadPoolExecutor executors = ThreadUtil.newFixedExecutor(20, "数据表比对", true);


    public TableDiffVo tableDiff() {
        List<Table> masterTableList = masterSqlExecMapper.selectAllTable();
        List<Table> targetTableList = targetSqlExecMapper.selectAllTable();

        List<Table> leftTableList = new ArrayList<>(masterTableList);
        leftTableList.removeAll(targetTableList);
        leftTableList = leftTableList.stream().sorted(Comparator.comparing(Table::getName)).toList();
        List<Table> rightTableList = new ArrayList<>(targetTableList);
        rightTableList.removeAll(masterTableList);
        rightTableList = rightTableList.stream().sorted(Comparator.comparing(Table::getName)).toList();

        List<Table> tableList = (List<Table>) CollUtil.intersection(masterTableList, targetTableList);
        tableList = tableList.stream().sorted(Comparator.comparing(Table::getName)).toList();

        return new TableDiffVo(masterTableList, targetTableList, tableList, leftTableList, rightTableList);
    }

    public List<TableDDL> tableDDL(String tableName) {
        List<TableDDL> tableDDLList = masterSqlExecMapper.selectTableDDL(tableName);
        tableDDLList = Optional.ofNullable(tableDDLList).orElse(Collections.emptyList()).stream()
            .sorted(Comparator.comparing(TableDDL::getFieldSeq)).toList();
        return tableDDLList;
    }

    public Map<String, List<String>> ddlDiff(boolean normal) {
        Map<String, List<String>> result = MapUtil.newHashMap(true);
        List<String> tableNameList = normal ? masterSqlExecMapper.selectAllTableName() : targetSqlExecMapper.selectAllTableName();
        for (String tableName : tableNameList) {
            List<TableDDL> sourceTableDDLList;
            List<TableDDL> targetTableDDLList;
            sourceTableDDLList = normal ? masterSqlExecMapper.selectTableDDL(tableName) : targetSqlExecMapper.selectTableDDL(tableName);
            targetTableDDLList = normal ? targetSqlExecMapper.selectTableDDL(tableName) : masterSqlExecMapper.selectTableDDL(tableName);

            sourceTableDDLList = Optional.ofNullable(sourceTableDDLList).orElse(Collections.emptyList()).stream()
                .sorted(Comparator.comparing(TableDDL::getFieldSeq)).toList();
            targetTableDDLList = Optional.ofNullable(targetTableDDLList).orElse(Collections.emptyList()).stream()
                .sorted(Comparator.comparing(TableDDL::getFieldSeq)).toList();
            Map<String, List<String>> map = ddlDiff(tableName, sourceTableDDLList, targetTableDDLList);
            if (MapUtil.isNotEmpty(map)) {
                result.putAll(map);
            }
        }
        return result;
    }

    private void addDiff(TableDDL sourceDDL, TableDDL targetDDL, List<String> diffList) {
        if (targetDDL == null) {
            diffList.add("表:" + sourceDDL.getTableName() + "<->");
            return;
        }
        if (!Objects.equals(sourceDDL.getTableDesc(), targetDDL.getTableDesc())) {
            diffList.add("表注释:" + sourceDDL.getFieldName() + "->" + sourceDDL.getTableDesc() + "<->" + targetDDL.getTableDesc());
        }
        if (!Objects.equals(sourceDDL.getFieldDesc(), targetDDL.getFieldDesc())) {
            diffList.add("字段注释:" + sourceDDL.getFieldName() + "->" + sourceDDL.getFieldDesc() + "<->" + targetDDL.getFieldDesc());
        }
        if (!Objects.equals(sourceDDL.getFieldType(), targetDDL.getFieldType())) {
            diffList.add("字段类型:" + sourceDDL.getFieldName() + "->" + sourceDDL.getFieldType() + "<->" + targetDDL.getFieldType());
        }
        if (!Objects.equals(sourceDDL.getPk(), targetDDL.getPk())) {
            diffList.add("字段主键:" + sourceDDL.getFieldName() + "->" + sourceDDL.getPk() + "<->" + targetDDL.getPk());
        }
        if (!Objects.equals(sourceDDL.getDefaultValue(), targetDDL.getDefaultValue())) {
            diffList.add("字段默认值:" + sourceDDL.getFieldName() + "->" + sourceDDL.getDefaultValue() + "<->" + targetDDL.getDefaultValue());
        }
        if (!Objects.equals(sourceDDL.getIdentity(), targetDDL.getIdentity())) {
            diffList.add("字段自增:" + sourceDDL.getFieldName() + "->" + sourceDDL.getIdentity() + "<->" + targetDDL.getIdentity());
        }
    }

    public Map<String, List<String>> ddlDiff(String tableName, List<TableDDL> sourceTableDDLList, List<TableDDL> targetTableDDLList) {
        Map<String, List<String>> result = Maps.newHashMap();
        Map<String, TableDDL> sourceFieldInfoMap = MapUtil.newHashMap(true);
        for (TableDDL tableDDL : sourceTableDDLList) {
            sourceFieldInfoMap.put(tableDDL.getFieldName(), tableDDL);
        }
        Map<String, TableDDL> targetFieldInfoMap = MapUtil.newHashMap(true);
        for (TableDDL tableDDL : targetTableDDLList) {
            targetFieldInfoMap.put(tableDDL.getFieldName(), tableDDL);
        }
        List<String> diffList = ListUtil.list(false);
        sourceFieldInfoMap.forEach((fieldName, sourceDDL) -> {
            TableDDL targetDDL = targetFieldInfoMap.get(fieldName);
            addDiff(sourceDDL, targetDDL, diffList);
            targetFieldInfoMap.remove(fieldName);
        });
        if (CollUtil.isNotEmpty(diffList)) {
            result.put(tableName, diffList);
        }
        targetFieldInfoMap.forEach((fieldName, targetDDL) -> diffList.add("字段__:" + "<->字段名:" + targetDDL.getFieldName() + ",描述:" + targetDDL.getFieldDesc() + ",类型:" + targetDDL.getFieldType() + ",主键:" + targetDDL.getPk() + ",非空:" + targetDDL.getNotNUll() + ",默认值:" + targetDDL.getDefaultValue() + ",自增:" + targetDDL.getIdentity()));
        return result;
    }

    private void validateDDLSame(String tableName, boolean normal) {
        List<TableDDL> sourceTableDDLList;
        List<TableDDL> targetTableDDLList;
        sourceTableDDLList = normal ? masterSqlExecMapper.selectTableDDL(tableName) : targetSqlExecMapper.selectTableDDL(tableName);
        targetTableDDLList = normal ? targetSqlExecMapper.selectTableDDL(tableName) : masterSqlExecMapper.selectTableDDL(tableName);

        sourceTableDDLList = Optional.ofNullable(sourceTableDDLList).orElse(Collections.emptyList()).stream()
            .sorted(Comparator.comparing(TableDDL::getFieldSeq)).toList();
        targetTableDDLList = Optional.ofNullable(targetTableDDLList).orElse(Collections.emptyList()).stream()
            .sorted(Comparator.comparing(TableDDL::getFieldSeq)).toList();
        Map<String, List<String>> map = ddlDiff(tableName, sourceTableDDLList, targetTableDDLList);

        Assert.isTrue(MapUtil.isEmpty(map), "表名" + tableName + "数据结构不一致,请修正后再做对比");
    }

    public List<String> dataDiff(String prefix, boolean normal) {
        List<String> result = ListUtil.list(true);
        List<String> tableNameAllList = normal ? masterSqlExecMapper.selectAllTableName() : targetSqlExecMapper.selectAllTableName();
        List<String> tableNameList = Optional.ofNullable(tableNameAllList).orElse(Collections.emptyList()).stream()
            .filter(v -> v.startsWith(prefix)).toList();
        if (CollUtil.isEmpty(tableNameList)) {
            return Collections.emptyList();
        }
        for (String tableName : tableNameList) {
            validateDDLSame(tableName, normal);
        }
        List<CompletableFuture<Void>> futures = Lists.newArrayListWithExpectedSize(tableNameList.size());
        for (String tableName : tableNameList) {
            futures.add(CompletableFuture.runAsync(() -> {
                List<TableDDL> tableDDLList = normal ? masterSqlExecMapper.selectTableDDL(tableName) : targetSqlExecMapper.selectTableDDL(tableName);
                List<TableDDL> tablePkDDLList = tableDDLList.stream().filter(v -> Objects.equals(true, v.getPk()))
                    .sorted(Comparator.comparing(TableDDL::getFieldSeq)).toList();
                List<String> pkStr = tablePkDDLList.stream().map(TableDDL::getFieldName).toList();
                String selectLimit5000 = SELECT_FROM + tableName + " order by " + CharSequenceUtil.join(",", pkStr) + " limit 5000 ";
                List<Map<String, Object>> sourceDataList = normal ? masterSqlExecMapper.selectList(selectLimit5000) : targetSqlExecMapper.selectList(selectLimit5000);
                compareData(tableName, sourceDataList, tablePkDDLList, tableDDLList, normal, result);
            }, executors));
        }
        CompletableFuture.allOf(futures.toArray(new CompletableFuture[futures.size()])).join();
        return result;
    }

    private void compareData(String tableName, List<Map<String, Object>> sourceDataList, List<TableDDL> tablePkDDLList, List<TableDDL> tableDDLList, boolean normal, List<String> result) {
        Map<String, List<Long>> pkMap = Maps.newHashMap();
        for (Map<String, Object> stringObjectMap : sourceDataList) {
            for (TableDDL tableDDL : tablePkDDLList) {
                List<Long> pkList = pkMap.get(tableDDL.getFieldName());
                if (CollUtil.isEmpty(pkList)) {
                    pkMap.put(tableDDL.getFieldName(), Lists.newArrayList(Long.parseLong(stringObjectMap.get(tablePkDDLList.get(0)
                        .getFieldName()).toString())));
                } else {
                    pkList.add(Long.parseLong(stringObjectMap.get(tablePkDDLList.get(0).getFieldName()).toString()));
                }
            }
        }
        String sql = SELECT_FROM + tableName + WHERE;
        StringBuilder whereCondition = new StringBuilder();
        int index = 0;
        for (TableDDL tableDDL : tablePkDDLList) {
            if (index != 0) {
                whereCondition.append(AND);
            }
            whereCondition.append(tableDDL.getFieldName()).append(" in (")
                .append(CharSequenceUtil.join(",", pkMap.get(tableDDL.getFieldName()))).append(")");
            index++;
        }
        sql = sql + whereCondition;
        List<Map<String, Object>> targetDataList = normal ? targetSqlExecMapper.selectList(sql) : masterSqlExecMapper.selectList(sql);
        Map<String, Map<String, Object>> targetDataMap = MapUtil.newHashMap();
        for (Map<String, Object> dataMap : targetDataList) {
            StringBuilder pk = new StringBuilder();
            for (TableDDL pkDDL : tablePkDDLList) {
                pk.append(",").append(dataMap.get(pkDDL.getFieldName()).toString());
            }
            targetDataMap.put(pk.toString(), dataMap);
        }
        for (Map<String, Object> sourceDataMap : sourceDataList) {
            StringBuilder pk = new StringBuilder();
            for (TableDDL pkDDL : tablePkDDLList) {
                pk.append(",").append(sourceDataMap.get(pkDDL.getFieldName()).toString());
            }
            StringBuilder tmp = new StringBuilder();
            for (TableDDL tableDDL : tablePkDDLList) {
                if (index != 0) {
                    whereCondition.append(AND);
                }
                tmp.append(tableDDL.getFieldName()).append("=").append(sourceDataMap.get(tableDDL.getFieldName()));
                index++;
            }
            Map<String, Object> targetMap = targetDataMap.getOrDefault(pk.toString(), MapUtil.newHashMap());
            if (MapUtil.isEmpty(targetMap)) {
                List<String> filedNameList = Lists.newArrayListWithExpectedSize(tableDDLList.size());
                List<String> valueList = Lists.newArrayListWithExpectedSize(tableDDLList.size());
                for (TableDDL tableDDL : tableDDLList) {
                    filedNameList.add(tableDDL.getFieldName());
                    if (isStr(tableDDL.getFieldType())) {
                        if (sourceDataMap.get(tableDDL.getFieldName()) == null) {
                            valueList.add(null);
                        } else {
                            valueList.add("'" + sourceDataMap.get(tableDDL.getFieldName()).toString() + "'");
                        }
                    } else {
                        if (sourceDataMap.get(tableDDL.getFieldName()) == null) {
                            valueList.add(null);
                        } else {
                            valueList.add(sourceDataMap.get(tableDDL.getFieldName()).toString());
                        }
                    }
                }
                String insert = "insert into " + tableName + " (" + CharSequenceUtil.join(",", filedNameList) + ") values (" + CharSequenceUtil.join(",", valueList) + ")";
                result.add(insert);
                String del = DELETE_FROM + tableName + WHERE + tmp;
                result.add(del);
            } else {
                for (TableDDL tableDDL : tableDDLList) {
                    if (!Objects.equals(sourceDataMap.get(tableDDL.getFieldName()), targetMap.get(tableDDL.getFieldName()))) {
                        String update = "update " + tableDDL.getTableName() + " set " + tableDDL.getFieldName() + "=" + (isStr(tableDDL.getFieldType()) ? "'" + sourceDataMap.get(tableDDL.getFieldName()) + "'" : sourceDataMap.get(tableDDL.getFieldName())) + WHERE + tmp;
                        result.add(update);
                    }
                }
            }
        }
    }

    private boolean isStr(String str) {
        return str.contains("time") || str.contains("date") || str.contains("datetime") || str.contains("char");
    }
}
